package cam.School.DataAccessObjectImpl;

import static cam.School.DataAccessObject.DAOUtil.close;
import static cam.School.DataAccessObject.DAOUtil.prepareStatement;
import static cam.School.DataAccessObject.DAOUtil.toSqlDate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cam.School.DAOException.DAOException;
import cam.School.DataAccessObject.DAOFactory;
import cam.School.DataAccessObject.SubjectDAO;
import cam.School.DataTransfertObject.CallResult;
import cam.School.DataTransfertObject.ClassRoomDTO;
import cam.School.DataTransfertObject.SubjectDTO;


public class SubjectDAOImpl implements SubjectDAO {
	

	private static final String SQL_FIND_ALL_SUBJECT_BY_EMPLOYEE = "SELECT "
			+ " SubjectId, professorId, name, coefficient, classname "
			+ " FROM subjectprofessor "
			+ " inner join subject on subject.id = subjectprofessor.subjectId"; 
	
	private static final String SQL_FIND_ALL_SUBJECT = "SELECT "
			+"id, name, coefficient, className "
			+"FROM subject";

	
	private static final String SQL_FIND_ALL_SUBJECT_NAME = "SELECT "
			+"DISTINCT name "
			+"FROM subject";
	
	private static final String SQL_INSERT_PROFESSOR_SUBJECT = "INSERT INTO subjectprofessor "
			+"(subjectId, professorId) VALUES(?,?)";
	private static final String REMOVE_PROFESSOR_SUBJECTS = "DELETE FROM subjectprofessor WHERE employeeId = ?";
	
	// Vars-----------------------------------------------------------------------------------
	private DAOFactory daoFactory;
	
	// Constructors ---------------------------------------------------------------------------

	public SubjectDAOImpl(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}
	@Override
	public SubjectDTO getById(Long id) throws DAOException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SubjectDTO getByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	private SubjectDTO mapSubject(ResultSet resultSet, int option)	throws SQLException {
		SubjectDTO subject = new SubjectDTO();	
		switch(option){
		case 0:
			subject.setId(resultSet.getLong("id"));	
			subject.setClassRoomName(resultSet.getString("className"));
			break;
		case 1:
			subject.setId(resultSet.getLong("SubjectId"));		
			subject.setProfessorId(resultSet.getLong("professorId"));		
			subject.setClassRoomName(resultSet.getString("classname"));
			break;
		default:
			subject.setName(resultSet.getString("name"));
			return subject;
		}
		subject.setName(resultSet.getString("name"));
		subject.setCoefficient(resultSet.getLong("coefficient"));
		return subject;
	}
	
	@Override
	public List<SubjectDTO> getAllByEmployee() throws DAOException {
		return getAll(SQL_FIND_ALL_SUBJECT_BY_EMPLOYEE, 1);
	}
	
	@Override
	public List<SubjectDTO> getAllSubject() throws DAOException {
		return getAll(SQL_FIND_ALL_SUBJECT, 0);
	}
			
	public List<SubjectDTO> getAll(String query, int option) throws DAOException {
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<SubjectDTO> subjects = new ArrayList<SubjectDTO>();

		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(query);
			
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				subjects.add(mapSubject(resultSet, option));
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}
		return subjects;
	}

	@Override
	public CallResult create(List<SubjectDTO> subjects, long employeeId) throws IllegalArgumentException,
			DAOException {		

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet generatedKeys = null;

		try {
			connection = daoFactory.getConnection();
			
			for(int i = 0; i < subjects.size(); i++){
				Object[] values = {
						subjects.get(i).getId(),
						employeeId
				};
				preparedStatement = prepareStatement(connection, SQL_INSERT_PROFESSOR_SUBJECT, false,
					values);
				int affectedRows = preparedStatement.executeUpdate();			
				if (affectedRows == 0) {				
					return new CallResult(404,"Not created");			
				} 
			}
			return  new CallResult(200,"Success");	
			
		} catch (SQLException e) {
			return new CallResult(406,e.toString());
		} finally {
			close(connection, preparedStatement, generatedKeys);
		}		
	}

	@Override
	public void update(SubjectDTO subject) throws IllegalArgumentException,
			DAOException {
		// TODO Auto-generated method stub
		
	}
	
	

	@Override
	public void delete(int subjectId) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public List<SubjectDTO> getAllSubjectName() throws DAOException {
		return getAll(SQL_FIND_ALL_SUBJECT_NAME, 2);
	}
	@Override
	public CallResult updateSubjectList(List<SubjectDTO> subject,
			long employeeId) throws IllegalArgumentException, DAOException {

		//Delete the old subjects
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			connection = daoFactory.getConnection();
			preparedStatement = connection.prepareStatement(REMOVE_PROFESSOR_SUBJECTS);
			
			resultSet = preparedStatement.executeQuery();			
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(connection, preparedStatement, resultSet);
		}

		return create(subject, employeeId);
	}

	

}
